<?php 
	class Test_volumes extends CI_Model{

	 	function __construct(){
        	parent::__construct();
    	}

    	function get_test_volume_list($limit = 0, $offset = 10, $conditions = array()){
    		$where = " WHERE 1=1";
    		if(!empty($conditions['subject_id'])){
                $where .= " AND tv.subject_id = '{$conditions['subject_id']}'";
            }
            if(!empty($conditions['school_id'])){
                $where .= " AND tv.school_id = '{$conditions['school_id']}'";
            }
            if(!empty($conditions['test_volume_name'])){
                $where .= ' AND tv.name like "%'.$conditions['test_volume_name'].'%"';
            }
            if(!empty($conditions['st'])){
                $conditions['st'] = strtotime($conditions['st']);
                $where .= " AND tv.add_time > {$conditions['st']} ";
            }
            if(!empty($conditions['et'])){
                $conditions['et'] = strtotime($conditions['et']) + 86400;
                $where .= " AND tv.add_time < {$conditions['et']} ";
            }
    		$sql = "SELECT SQL_CALC_FOUND_ROWS tv.*, t.teacher_name, s.title FROM {$this->db->dbprefix}tk_test_volumes AS tv ";
    		$sql .= "LEFT JOIN {$this->db->dbprefix}b_subject AS s ON(s.id=tv.subject_id) ";
    		$sql .= "LEFT JOIN {$this->db->dbprefix}o2o_teachers AS t ON(t.id=tv.teacher_id) ";
    		$sql .= "$where ORDER BY id DESC LIMIT $limit, $offset ";
    		$result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
    	}

        function get_test_volume_publish($limit = 0, $offset = 0, $conditions = array()){
            $where = " WHERE 1=1";
            if(!empty($conditions['volume_id'])){
                $where .= " AND volume_id = '{$conditions['volume_id']}'";
            }
            if(!empty($conditions['school_id'])){
                $where .= " AND school_id = '{$conditions['school_id']}'";
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM {$this->db->dbprefix}tk_test_volumes_publish $where ORDER BY id DESC ";
            if($limit > 0 || $offset > 0){
                $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            if($limit > 0 || $offset > 0){
                $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
                return array("count" => $row_num['row_num'], "result" => $result);
            }else{
                return $result;
            }
        }

        function get_volume_publish_list($limit = 0, $offset = 0, $conditions = array()){
            $where = " WHERE tvp.school_id = '{$this->user_data['school_id']}'";
            if(!empty($conditions['type'])){
                $where .= " AND tvp.type = '{$conditions['type']}'";
            }
            if(!empty($conditions['subject_id'])){
                $where .= " AND tv.subject_id = '{$conditions['subject_id']}'";
            }
            if(!empty($conditions['home_work_name'])){
                $where .= ' AND tvp.name like "%'.$conditions['home_work_name'].'%"';
            }
            if(!empty($conditions['classes_id'])){
                $where .= "AND find_in_set('{$conditions['classes_id']}',classes)";
            }

            if(!empty($conditions['status'])){
                $time = time();
                if($conditions['status'] == 1){
                    $where .= "AND tvp.start_time <= $time AND tvp.end_time > $time";
                }elseif($conditions['status'] == 2){
                    $where .= "AND tvp.start_time <= $time AND tvp.end_time <= $time";
                }
            }

            if(!empty($conditions['start_time'])){
                $where .= "AND date_format(from_unixtime(tvp.start_time),'%Y-%m-%d') = '{$conditions['start_time']}'";
            }

            if(!empty($conditions['end_time'])){
                 $where .= "AND date_format(from_unixtime(tvp.end_time),'%Y-%m-%d') = '{$conditions['end_time']}'";
            }

            $sql = "SELECT SQL_CALC_FOUND_ROWS tvp.name AS pname, tvp.id AS p_id, tvp.start_time, tvp.timeout, tv.*, t.teacher_name, s.title FROM {$this->db->dbprefix}tk_test_volumes_publish AS tvp ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}tk_test_volumes AS tv ON(tv.id=tvp.volume_id)";
            $sql .= "LEFT JOIN {$this->db->dbprefix}b_subject AS s ON(s.id=tv.subject_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_teachers AS t ON(t.id=tvp.teacher_id) ";
            $sql .= "$where ORDER BY id DESC ";
            if($limit > 0 || $offset > 0){
                $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            if($limit > 0 || $offset > 0){
                $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
                return array("count" => $row_num['row_num'], "result" => $result);
            }else{
                return $result;
            }
        }

        function get_volume_publish_history($limit = 0, $offset = 0, $conditions = array()){
            $where = " WHERE 1=1";
            if(!empty($conditions['type'])){
                $where .= " AND home_work_h.type = '{$conditions['type']}'";
            }
            
            if(!empty($conditions['home_work_id'])){
                $where .= " AND home_work_h.data_id = '{$conditions['home_work_id']}'";
            }

            if(!empty($conditions['name'])){
                $where .= " AND s.name like '%{$conditions['name']}%'";
            }
            
            $sql = "SELECT SQL_CALC_FOUND_ROWS home_work_h.*, s.name FROM {$this->db->dbprefix}tk_test_volume_historys AS home_work_h ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_student AS s ON(s.user_id=home_work_h.uid) ";
            $sql .= "$where ORDER BY id DESC LIMIT $limit, $offset ";
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function delete_volume_data($volume_id){
            $this->db->trans_start();
            $sql = "DELETE FROM {$this->db->dbprefix}tk_test_volumes WHERE id='{$volume_id}'";
            $this->db->query($sql);
            $sql = "DELETE FROM {$this->db->dbprefix}tk_test_volume_historys WHERE volume_id='{$volume_id}'";
            $this->db->query($sql);
            $sql = "DELETE FROM {$this->db->dbprefix}tk_test_volumes_publish WHERE volume_id='{$volume_id}'";
            $this->db->query($sql);
            $this->db->trans_complete();
        }
    }